package jdbc;/*
 *
 * @author 陈粒
 * @version 创建时间：2021/7/30 14:15
 */

import java.sql.*;
import java.util.Arrays;

public class JDBC_ExecuteQuery {
    public static void main(String[] args) {
        //创建全局连接对象
        Connection conn = null;
        //创建全局传输对象
        Statement stmt = null;
        //创建全局结果集对象
        ResultSet resultSet = null;
        try {
            //1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.创建连接对象
            conn = DriverManager.getConnection("jdbc:mysql:///teacher", "root", "root");
            //3.创建传输对象
            stmt = conn.createStatement();
            //4.执行sql语句
            resultSet = stmt.executeQuery("SELECT sname,  sum(degree) total_score from students a,scores b where degree > 0 and a.sno = b.sno and sname='李军'");
            //5.打印结果
            while (resultSet.next()) {
                String sname = resultSet.getString(1);
                double total_score = resultSet.getDouble(2);
                System.out.println(sname +" "+ total_score);
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            //6.释放资源
            if (resultSet !=null){
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(stmt !=null){
                try {
                    stmt.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(conn !=null){
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }
}